package dnbW.dbW.sucxentdbW;

import dnbW.uiW.queryW.modelW.JoinNodeObject;
import dnbW.dbW.DataSource;
import dnbW.dbW.PathExpression;
import dnbW.entityW.*;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * The translator in charge of producing SUCXENT++ related queries
 * @author Truong Ba Quan
 */
public class SucxentQueryTranslator {
    // <editor-fold defaultstate="collapsed" desc="Index">
    private int index = 0;
    
    public static int PATHID;

    private String produceIndexString() {
        index++;
        return "V" + String.valueOf(index);
    }
    // </editor-fold>
    
    // <editor-fold defaultstate="collapsed" desc="Temporary Table Name">
    private String tempTableName;
    // </editor-fold>

    public String translate(Condition outputCondition, String tempTableName) {
        this.tempTableName = tempTableName;
        StringBuilder result = new StringBuilder();

        result.append(this.translateCondition(outputCondition, null, null, ""));
        if (result.toString().trim().equals("")) {
            return "No valid nodes can be found!!!! ^_^";
        }
        else {
            result.append("\n ORDER BY DocId, AncestorDeweyGroup");
            if (result.indexOf("DocumentRValue") != -1) {
                result.append("\n OPTION (force order, order group, hash join)");
            }
            else {
                result.append("\n OPTION (force order, order group)");
            }
        }
        System.out.println(result.toString());
        return result.toString();
    }

    private String translateCondition(Condition condition, Condition parentCondition, String parentPathIndex, String tab) {
        String indexString = this.produceIndexString();

        Path path = condition.getPath();

        String selectedColumns = indexString + ".DocId, AncestorDeweyGroup";
        // if the current path is the outmost return path
        if (tab.equals("") && this.tempTableName != null) {
            selectedColumns = selectedColumns + " into " + this.tempTableName;
        }

        String fromTables;
        if (!path.isAttribute()) {
            fromTables = path.getDataSource().getName() + "_PathValue " + indexString;
        }
        else {
            fromTables = path.getDataSource().getName() + "_Attribute " + indexString;
        }
        
        StringBuilder whereClauses = new StringBuilder();
        whereClauses.append(" WHERE PathId IN " + produceIdLeafList(path));

        if (!path.isAttribute()) {
            whereClauses.append(" and BranchOrder < " + path.getLevel());
        }

        if (parentCondition != null) {
            if (condition.getOperator() != null) {
                String value = condition.getValue();
                switch (condition.getOperator().getType()) {
                    case Equal:
                    case Unequal: value = '\'' + value + '\''; break;
                    case Contain: value = "\'%" + value + "%\'";
                }
                whereClauses.append(" and " + indexString + ".LeafValue " + condition.getOperator().getSQLString() + " " + value);
            }

            if (path.getDataSource() == parentCondition.getPath().getDataSource()) {
                if (path.getDataSource().getNumDocuments() != 1)
                    whereClauses.append(" and " + indexString + ".DocId = " + parentPathIndex + ".DocId");

                int lcaLevel = Path.findLCALevel(path, parentCondition.getPath());

                if (lcaLevel > 1) {
                    if (path.getDataSource().getNumDocuments() == 1) {
                        long ancestorValue = path.getDataSource().tryGetDocument(1).getAncestorValue(lcaLevel-1);
                        String ancestorValueString = " cast (" + ancestorValue + " AS bigint) ";
                        whereClauses.append(" and " + indexString + ".AncestorDeweyGroup/" + ancestorValueString + " = " + parentPathIndex + ".AncestorDeweyGroup/" + ancestorValueString);
                    }
                    else {
                        fromTables = fromTables + " join " + path.getDataSource().getName() + "_DocumentRValue R" + indexString
                                        + " on " + indexString + ".DocId = R" + indexString + ".DocId";
                        String ancestorValueString = " R" + indexString + ".AncestorValue ";
                        whereClauses.append(" and R" + indexString + ".Level = " + (lcaLevel-1) + " and "
                                + indexString + ".AncestorDeweyGroup/" + ancestorValueString
                                + " = " + parentPathIndex + ".AncestorDeweyGroup/" + ancestorValueString);
                    }
                }
            }
            else {
                whereClauses.append(" and " + indexString + ".LeafValue = " + parentPathIndex + ".LeafValue");
            }
        }

        whereClauses.append(translateConditionGroup(condition.getGroup(), condition, indexString, tab + "\t"));
        String result = "SELECT " + selectedColumns + " FROM " + fromTables + whereClauses.toString();
        return result;
    }

    private String translateConditionGroup(ConditionGroup group, Condition parentCondition, String parentPathIndex, String tab) {
        if (group.getConditions().isEmpty()) return "";
        StringBuilder result = new StringBuilder();
        result.append("\n" + tab + " AND (");

        boolean first = true;
        for (Condition condition : group.getConditions()) {
            if (first) {
                result.append("\n" + tab + "EXISTS (");
            }
            else {
                if (group.getOperator() == ConditionGroup.Operator.And) {
                    result.append("\n" + tab + "AND EXISTS (");
                }
                if (group.getOperator() == ConditionGroup.Operator.Or) {
                    result.append("\n" + tab + "OR EXISTS (");
                }
            }
            result.append(this.translateCondition(condition, parentCondition, parentPathIndex, tab));
            result.append(")");
            first = false;
        }
        result.append(")");
        return result.toString();
    }

    public static String translateJoinQuery(JoinNodeObject join) {
        if (join == null) return null;
        String sqlQuery = "select V1.DocId as DocId1, V1.DeweyOrderSum as Dewey1, V2.DocId as DocId2, V2.DeweyOrderSum as Dewey2\n" +
                            "into #TableName#\n" +
                            "from Interpro_PATHVALUE V2, Interpro_ATTRIBUTE A2, Uniprot_ATTRIBUTE A1, Uniprot_PATHVALUE V1\n" +
                            "where A1.PathId in #PathId1#\n" +
                            "and A2.PathId in #PathId2#\n" +
                            "and V1.LeafOrder = A1.LeafOrder\n" +
                            "and V2.LeafOrder = A2.LeafOrder\n" +
                            "and A1.LeafValue = A2.LeafValue";
        sqlQuery = sqlQuery.replace("#TableName#", "Join" + join.getLeftPath().getDataSource().getName() + join.getRightPath().getDataSource().getName());
        sqlQuery = sqlQuery.replace("#PathId1#", produceIdLeafList(join.getLeftPath()));
        sqlQuery = sqlQuery.replace("#PathId2#", produceIdLeafList(join.getRightPath()));
        return sqlQuery;
    }

    public static String produceIdLeafList(PathExpression path) {
        String sqlQuery = "SELECT PathId FROM #DataSource#_Path WHERE PathExp LIKE '#PathExp#%'";
        sqlQuery = sqlQuery.replaceAll("#DataSource#", path.getDataSource().getName());
        sqlQuery = sqlQuery.replaceAll("#PathExp#", translatePathToDatabaseString(path));
        try {
            Statement statement = path.getContextDb().getDbConnection().createStatement();
            ResultSet results = statement.executeQuery(sqlQuery);
            StringBuilder builder = new StringBuilder();
            builder.append('(');
            if (results.next()){
                builder.append(results.getInt("PathId"));
                while (results.next()) {
                    builder.append(',');
                    builder.append(results.getInt("PathId"));
                }
            }
            builder.append(')');
            return builder.toString();
        }
        catch (SQLException ex) {
            System.err.println(ex.getMessage());
            return null;
        }
    }

    public static String produceIdLeafList(Path path) {
        String sqlQuery = "SELECT PathId FROM #DataSource#_Path WHERE PathExp LIKE '#PathExp#%'";
        sqlQuery = sqlQuery.replaceAll("#DataSource#", path.getDataSource().getName());
        sqlQuery = sqlQuery.replaceAll("#PathExp#", translatePathToDatabaseString(path));
        try {
            Statement statement = path.getDataSource().getDatabase().getDbConnection().createStatement();
            ResultSet results = statement.executeQuery(sqlQuery);
            StringBuilder builder = new StringBuilder();
            builder.append('(');
            if (results.next()){
                PATHID = results.getInt("PathId");
                builder.append(results.getInt("PathId"));
                while (results.next()) {
                    builder.append(',');
                    builder.append(results.getInt("PathId"));
                }
            }
            builder.append(')');
            return builder.toString();
        }
        catch (SQLException ex) {
            System.err.println(ex.getMessage());
            return null;
        }
    }

    /**
     * Translates the given path to the path expression used in the SUCXENT++ database
     * @param path
     * @return
     */
    private static String translatePathToDatabaseString(PathExpression path) {
        StringBuilder result = new StringBuilder();
        for (javax.swing.tree.DefaultMutableTreeNode step : path.getFullPath()) {
            result.append('.');
            result.append(step.toString());
            result.append('#');
        }
        return result.toString();
    }

    private static String translatePathToDatabaseString(Path path) {
        StringBuilder result = new StringBuilder();
        for (String element : path.getElements()) {
            result.append('.');
            result.append(element.toString());
            result.append('#');
        }
        return result.toString();
    }

    /**
     * Checks whether the given path is a leaf path
     * @param path
     * @return
     */
    public static boolean isPathLeaf(PathExpression path) {
        String sqlQuery = "select * from #DataSource#_Path where PathExp LIKE '#PathExp#'";
        sqlQuery = sqlQuery.replaceAll("#DataSource#", path.getDataSource().getName());
        sqlQuery = sqlQuery.replaceAll("#PathExp#", translatePathToDatabaseString(path));
        try {
            Statement statement = path.getContextDb().getDbConnection().createStatement();
            ResultSet results = statement.executeQuery(sqlQuery);
            // Is the result not empty?
            if (results.next()) {
                return true;
            }
            else return false;
        }

        catch (SQLException ex) {
            System.err.println(ex.getMessage());
            return false;
        }
    }

    public static boolean isSingleDoc(DataSource dataSource) {
        try {
            Statement statement = dataSource.getDatabase().getDbConnection().createStatement();
            String query = "SELECT count(ID) AS Total FROM " + dataSource.getName() + "_Document\n";
            ResultSet results = statement.executeQuery(query);
            results.next();
            if (results.getInt("Total") == 1) {
                return true;
            }
            else return false;
        } catch (Exception ex) {
            System.err.println(ex.getMessage());
            return false;
        }
    }
}
